import pandas as pd
import matplotlib.pyplot as plt

# 文件路径
file_path = r"python\sh000300\000300perf.xlsx" # 替换为您的文件路径

# 读取 Excel 文件
data = pd.read_excel(file_path)

# 查看数据
print("数据预览：")
print(data.head())

# 数据预处理
# 将日期列设置为索引
data['Date'] = pd.to_datetime(data['日期Date'], format='%Y%m%d')
data.set_index('Date', inplace=True)

# 选择关键列
selected_data = data[['收盘Close', '涨跌幅(%)Change(%)']]

# 添加每日收益率列
selected_data['Return'] = selected_data['收盘Close'].pct_change()

# 计算简单移动平均线（MA）
selected_data['MA5'] = selected_data['收盘Close'].rolling(window=5).mean()
selected_data['MA20'] = selected_data['收盘Close'].rolling(window=20).mean()

# 描述性统计
print("\n描述性统计：")
print(selected_data.describe())

# 绘制收盘价走势
plt.figure(figsize=(12, 6))
plt.plot(selected_data['收盘Close'], label='Close Price')
plt.title('CSI300 Index Close Price')
plt.xlabel('Date')
plt.ylabel('Price (CNY)')
plt.legend()
plt.show()

# 绘制均线图
plt.figure(figsize=(12, 6))
plt.plot(selected_data['收盘Close'], label='Close Price')
plt.plot(selected_data['MA5'], label='5-day Moving Average')
plt.plot(selected_data['MA20'], label='20-day Moving Average')
plt.title('CSI300 Index with Moving Averages')
plt.xlabel('Date')
plt.ylabel('Price (CNY)')
plt.legend()
plt.show()

# 保存数据
selected_data.to_excel("processed_000300perf.xlsx")